﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SD.IDAL;
using SD.Entities;
using System.Data.SqlClient;
using System.Data;

namespace SD.DAL
{
    public class TownDAO : BaseDAO, ITownDAO
    {
        #region ITownDAO Members

        /// <summary>
        /// Get all town
        /// </summary>
        /// <returns></returns>
        public List<Town> GetAll()
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "TownGetAll";

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            // Create new list town
            List<Town> list = new List<Town>();

            // Add each town in table to list
            foreach (DataRow row in dt.Rows)
            {
                Town t = new Town();
                t.TownID = row["TownID"].ToString();
                t.Name = row["Name"].ToString();
                list.Add(t);
            }

            // return list
            return list;
        }

        /// <summary>
        /// Get town by id
        /// </summary>
        /// <param name="townId"></param>
        /// <returns></returns>
        public Town GetById(string townId)
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "TownGetById";

            // Add parameters to stored procedure
            comm.Parameters.AddWithValue("@TownID", townId);

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            Town t = null;

            // if table has data
            if (dt.Rows.Count > 0)
            {
                t = new Town();
                t.TownID = dt.Rows[0]["TownID"].ToString();
                t.Name = dt.Rows[0]["Name"].ToString();
            }

            // return town
            return t;
        }

        /// <summary>
        /// Insert new town
        /// </summary>
        /// <param name="town"></param>
        /// <returns></returns>
        public bool Insert(Town town)
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "TownInsert";

            // Add parameters to stored procedure
            comm.Parameters.AddWithValue("@Name", town.Name);

            int result = -1;
            try
            {
                // Execute stored procedure
                result = ExecuteNonQuery(comm);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // return true if insert successfully, otherwise return false
            return (result != -1);
        }

        /// <summary>
        /// Update town
        /// </summary>
        /// <param name="town"></param>
        /// <returns></returns>
        public bool Update(Town town)
        {
            return false;
        }

        /// <summary>
        ///  Update town
        /// </summary>
        /// <param name="townId"></param>
        /// <returns></returns>
        public bool Delete(string townId)
        {
            return false;
        }

        #endregion

        #region ITownDAO Members

        /// <summary>
        /// Get town by name
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public Town GetByName(string name)
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "TownGetByName";

            // Add parameter to stored procedure
            comm.Parameters.AddWithValue("@Name", name);

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            Town t = null;

            // if table has data
            if (dt.Rows.Count > 0)
            {
                DataRow row = dt.Rows[0];
                t.TownID = row["TownID"].ToString();
                t.Name = row["Name"].ToString();
            }

            return t;
        }

        #endregion
    }
}
